import json

from flask import Flask, request, jsonify
from flask_cors import CORS
import pymysql
import pandas as pd
from opcua import Client

# 数据库配置
conn_mysql = pymysql.connect(
    host="localhost",  # 数据库地址
    user="root",  # 数据库账号
    passwd="1234",  # 数据库密码
    db="app_data_2",  # 数据库
    port=3306,  # 数据库端口
    charset="utf8"  # 字符编码格式
)

# 创建OPC客户端
opc_client = Client("opc.tcp://laptop-aurora:53530/OPCUA/SimulationServer")
opc_client.connect()
# 获取节点
temper_node = opc_client.get_node("ns=3;i=1011")
volt_node = opc_client.get_node("ns=3;i=1009")
elect_node = opc_client.get_node("ns=3;i=1012")
speed_node = opc_client.get_node("ns=3;i=1013")
vibration_node = opc_client.get_node("ns=3;i=1010")
status_node = opc_client.get_node("ns=3;i=1014")

# 创建Flask实例
app = Flask(__name__)
CORS(app, supports_credentials=True)


# 分页查询学生表数据
@app.route('/')
@app.route('/getStuList', methods=["GET"])
def get_stu_list():
    # 获取参数
    page_size = int(request.args.get("pageSize", default=5))
    page_num = int(request.args.get("pageNum", default=1))
    offset = (page_num - 1) * page_size

    # SQL语句
    query_sql = "select * from student limit %d offset %d" % (page_size, offset)
    # 使用pandas提供的sql执行函数
    data = pd.read_sql(query_sql, conn_mysql)
    # 从数据库获取到的数据转换成json格式
    stu_data = data.to_json(orient="records")
    # 提交SQL事务
    conn_mysql.commit()

    # 返回数据给前端
    # print(stu_data)
    return stu_data


# 根据血型查询学生数据
@app.route('/getStuByBlond', methods=["GET"])
def get_stu_by_blonb():
    # 获取参数
    blond = request.args.get("blond")

    # SQL语句
    query_sql = "select * from student where blond = " + str(blond)
    # 使用pandas提供的sql执行函数
    data = pd.read_sql(query_sql, conn_mysql)
    # 从数据库获取到的数据转换成json格式
    stu_data = data.to_json(orient="records")
    # 提交SQL事务
    conn_mysql.commit()

    # 返回数据给前端
    # print(stu_data)
    return stu_data


# OPC交互
@app.route('/getNodeValue', methods=["GET"])
def getNodeValue():
    # 从节点获取数据
    temper = temper_node.get_value()
    volt = volt_node.get_value()
    elect = elect_node.get_value()
    speed = speed_node.get_value()
    vibration = vibration_node.get_value()
    status = status_node.get_value()
    data_json = jsonify({
        "temper": temper,
        "volt": volt,
        "elect": elect,
        "speed": speed,
        "vibration": vibration,
        "status": status
    })

    return data_json


# 修改工站状态
@app.route('/setStatus', methods=["POST"])
def setStatus():
    node_id = request.json.get("nodeId")
    value = request.json.get("value")

    node = opc_client.get_node(node_id)
    node.set_value(value)

    return jsonify({"status": True})


if __name__ == '__main__':
    app.run(host="127.0.0.1", port=8989)
